﻿using System;
using System.Text;
using System.Text.RegularExpressions;
using System.Collections.Generic;

namespace EnterpriseFramework.DataAccess.Provider
{
    public class Compatibility : ICompatibility
    {
        private System.Data.SqlClient.SqlConnection connection;
        public System.Data.IDbConnection Connection
        {
            get
            {
                if (connection == null)
                    connection = new System.Data.SqlClient.SqlConnection();
                return connection;
            }
        }
        /// <summary>
        /// Creates an insert command for given table.
        /// </summary>
        /// <param name="table">table for which to create an insert command</param>
        /// <returns>insert command</returns>
        public Command GetInsertCommand(Table table, QueryResult query)
        {
            return default(Command);
        }

        /// <summary>
        /// Returns the next value in a sequence for given table.
        /// If sequence if not defined or sequences are not used this method should return null.
        /// This method is called before an object is inserted into a given table. The returned
        /// value will be assigned to the auto-incremented column defined on a table.
        /// </summary>
        /// <param name="table">table into which the target object will be inserted</param>
        /// <returns>next value in a sequence for given table, or null if there is not sequence</returns>
        public object NextSequenceValue(Table table)
        {
            return default(Command);
        }

        /// <summary>
        /// Returns the last auto incremented value for given table.
        /// If there are no auto incremented values, this method should return null.
        /// If a non-null value is returned, it will be assigned to the auto increment
        /// column of the last inserted object.
        /// </summary>
        /// <param name="table">table into which a record was inserted</param>
        /// <param name="command">actual database command used to insert a new record</param>
        /// <returns>auto incremented value</returns>
        public object AutoIncrementedValue(Table table)
        {
            return default(Command);
        }

        /// <summary>
        /// Creates an update command for given table.
        /// </summary>
        /// <param name="table">table for which to create an update command</param>
        /// <returns>update command</returns>
        public Command GetUpdateCommand(Table table)
        {
            return default(Command);
        }

        /// <summary>
        /// Creates a delete command for given table to delete 1 record.
        /// </summary>
        /// <param name="table">table for which to create a delete command</param>
        /// <returns>delete command</returns>
        public Command GetDeleteCommand(Table table)
        {
            return default(Command);
        }

        /// <summary>
        /// Creates a delete command for given table using given query.
        /// NOTE: The query object may be null.
        /// </summary>
        /// <param name="table">table for which to create a delete command</param>
        /// <param name="query">query that identifies records to delete</param>
        /// <returns>delete command</returns>
        public Command GetDeleteCommand(Table table, QueryResult query)
        {

            return default(Command);
        }

        /// <summary>
        /// Creates a select command for given table using given query.
        /// NOTE: The query object may be null.
        /// </summary>
        /// <param name="table">table for which to create a select command</param>
        /// <param name="query">query that identifies records to select</param>
        /// <returns></returns>
        public Command GetSelectCommand(Table table, IQuery query)
        {

            int Top;
            Command cmd = new Command();
            AbstractColumn[] columns = table.Columns;
            StringBuilder buf = new StringBuilder().Append("SELECT ");
            List<IParameter> whereParameter = null, HavingParameter = null;
            string where = "", haveing = "", expression = "", orderby = "";
            bool groupby = false;
            if (query != null)
            {
                if (query.ResqultQuery.ContainsKey("WhereParameters"))
                    whereParameter = (List<IParameter>)query.ResqultQuery["WhereParameters"];
                if (query.ResqultQuery.ContainsKey("HavingParameters"))
                    HavingParameter = (List<IParameter>)query.ResqultQuery["HavingParameters"];
                if (query.ResqultQuery.ContainsKey("Where"))
                    where = " WHERE " + Convert.ToString(query.ResqultQuery["Where"]);
                if (query.ResqultQuery.ContainsKey("Having"))
                    haveing = " HAVING " + Convert.ToString(query.ResqultQuery["Having"]);
                if (query.ResqultQuery.ContainsKey("GroupBy"))
                    groupby = true;
                if (query.ResqultQuery.ContainsKey("Top"))
                {
                    if (Int32.TryParse(Convert.ToString(query.ResqultQuery["Top"]), out Top))
                        if (Top > 0)
                            buf.Append(" Top ").Append(Top).Append(" ");
                        else
                        {
                            //<e> 2
                        }
                    else
                    {
                        //<e> 3
                    }
                }

                if (query.ResqultQuery.ContainsKey("ColumnExpression"))
                    expression = Convert.ToString(query.ResqultQuery["ColumnExpression"]);

                if (query.ResqultQuery["OrderBy"] != null)
                    orderby = Convert.ToString(query.ResqultQuery["OrderBy"]);
            }

            bool append = false;

            StringBuilder fields = new StringBuilder();

            foreach (AbstractColumn column in columns)
            {
                if (append)
                    fields.Append(",");
                else
                    append = true;

                if (where.Contains(column.ColumnName))
                    where = Regex.Replace(where, column.ColumnName, "[" + column.Name + "]");
                if (whereParameter != null)
                    foreach (IParameter par in whereParameter)
                    {
                        if (par.UseFillParameter)
                        {
                            par.DbType = column.DBType;
                            par.Size = column.Size;
                        }
                    }

                if (HavingParameter != null)
                    foreach (IParameter par in HavingParameter)
                    {
                        if (par.UseFillParameter)
                        {
                            par.DbType = column.DBType;
                            par.Size = column.Size;
                        }
                    }

                orderby = orderby.Replace(column.ColumnName, column.Name);

                if (expression.Contains(column.ColumnName))
                {
                    expression = expression.Replace(column.Name, "[" + column.Name + "]");
                    continue;
                }
                fields.Append("[").Append(column.Name).Append("]");
            }
            if (!string.IsNullOrEmpty(expression))
                fields.Append(",").Append(expression);

            buf.Append(fields.ToString());
            buf.Append(" FROM ");
            if (!string.IsNullOrEmpty(table.Schema))
                buf.Append("[").Append(table.Schema).Append("].");
            buf.Append("[").Append(table.Name).Append("]");

            buf.Append(where);

            if (groupby)
                buf.Append(" GROUP BY ").Append(fields.ToString());

            if (!string.IsNullOrEmpty(orderby))
                buf.Append(" ORDER BY ").Append(orderby);
            cmd.Text = buf.ToString();
            if (whereParameter != null)
                cmd.Add(whereParameter);
            if (HavingParameter != null)
                cmd.Add(HavingParameter);
            return cmd;
        }

        public Command GetSelectCommand(Table table, QueryResult query)
        {
            Command cmd = new Command();
            AbstractColumn[] columns = table.Columns;
            StringBuilder buf = new StringBuilder().Append("SELECT ");

            if (query != null)
            {
                if (!string.IsNullOrEmpty(query.Distinct))
                    buf.Append("DISTINCT ");
                if (query.Top > 0)
                    buf.Append(" Top ").Append(query.Top).Append(" ");
                else
                {
                    //<e> 2
                }
            }

            bool append = false;

            StringBuilder fields = new StringBuilder();

            foreach (AbstractColumn column in columns)
            {
                if (append)
                    fields.Append(",");
                else
                    append = true;

                //if (query != null)
                //{
                //    if (query.WhereClause != null)
                //    {
                //        if (query.WhereClause.Criteria.Contains(column.ColumnName))
                //            query.WhereClause.Criteria = Regex.Replace(query.WhereClause.Criteria, column.ColumnName,
                //                "[" + column.Name + "]");
                //        if (query.WhereClause.Parameters != null)
                //            foreach (IParameter par in query.WhereClause.Parameters)
                //            {
                //                if (par.UseFillParameter)
                //                {
                //                    par.DbType = column.DBType;
                //                    par.Size = column.Size;
                //                }
                //            }
                //    }

                //    if (query.HavingClause != null)
                //    {
                //        if (query.HavingClause.Criteria.Contains(column.ColumnName))
                //            query.HavingClause.Criteria = Regex.Replace(query.HavingClause.Criteria, column.ColumnName,
                //                "[" + column.Name + "]");
                //        if (query.HavingClause.Parameters != null)
                //            foreach (IParameter par in query.HavingClause.Parameters)
                //            {
                //                if (par.UseFillParameter)
                //                {
                //                    par.DbType = column.DBType;
                //                    par.Size = column.Size;
                //                }
                //            }
                //    }


                //    if (!string.IsNullOrEmpty(query.OrderBy))
                //        query.OrderBy = query.OrderBy.Replace(column.ColumnName, column.Name);

                //    if (!string.IsNullOrEmpty(query.ColumnExpression))
                //        if (query.ColumnExpression.Contains(column.ColumnName))
                //        {
                //            query.ColumnExpression = query.ColumnExpression.Replace(column.Name, "[" + column.Name + "]");
                //            continue;
                //        }
                //}
                if (query != null && !string.IsNullOrEmpty(query.ColumnExpression))
                    if (!query.ColumnExpression.Contains(column.Name))
                        column.Included = false;

                fields.Append("[").Append(column.Name).Append("]");
            }

            if (query != null && !string.IsNullOrEmpty(query.ColumnExpression))
                buf.Append(query.ColumnExpression);
            else
                buf.Append(fields.ToString());

            buf.Append(" FROM ");
            if (!string.IsNullOrEmpty(table.Schema))
                buf.Append("[").Append(table.Schema).Append("].");
            buf.Append("[").Append(table.Name).Append("]");

            if (query != null)
            {
                if (query.WhereClause != null)
                    if (!string.IsNullOrEmpty(query.WhereClause.Criteria))
                        buf.Append(" WHERE " + query.WhereClause.Criteria);

                if (!string.IsNullOrEmpty(query.GroupBy))
                    buf.Append(" GROUP BY ").Append(fields.ToString());

                if (query.HavingClause != null)
                    if (!string.IsNullOrEmpty(query.HavingClause.Criteria))
                        buf.Append(" HAVING " + query.HavingClause.Criteria);

                if (!string.IsNullOrEmpty(query.OrderBy))
                    buf.Append(" ORDER BY ").Append(query.OrderBy);

                if (query.WhereClause != null)
                    if (query.WhereClause.Parameters != null)
                        cmd.Add(query.WhereClause.Parameters);
                if (query.HavingClause != null)
                    if (query.HavingClause.Parameters != null)
                        cmd.Add(query.HavingClause.Parameters);
            }
            cmd.Text = buf.ToString();

            return cmd;
        }

        /// <summary>
        /// Creates a query that would select a single row based on the
        /// primary key column of the given table. The query must have at least
        /// one (and usually the only) parameter whose value will be set to the
        /// searched key.
        /// </summary>
        /// <param name="table">target table</param>
        /// <returns>query that would select a single row</returns>
        public Command GetFindQuery(Table table)
        {
            return default(Command);
        }
    }
}
